﻿// copyright @ 2009 Farida
//Query for Employee with Product OwnerShip for a Particular Year or Month using Stored Procedure

using System;
using System.Data;
using MySql.Data.MySqlClient;


namespace ProjectMain
{
    class ClassStoredProcQuery
    {
        public static void StoredProcQuery()
        {

            ClassStoredProcQuery spemp = new ClassStoredProcQuery();
            int myChoice;

            do
            {

                Console.WriteLine("-------------------------------------------------------------------");
                Console.WriteLine("1. Query for Employee with Product OwnerShip for a particular Month");
                Console.WriteLine("2. Query for Employee with Product OwnerShip for a particular Year");
                Console.WriteLine("3. - Quit\n");
                Console.WriteLine("Choice (1,2,or 3): ");
                // Retrieve the user's choice
                myChoice = Convert.ToInt16(Console.ReadLine());

                switch (myChoice)
                {
                    case 1: Console.WriteLine();
                        Console.WriteLine(" Query for Employee with Product OwnerShip for a particular Month");
                        Console.WriteLine("_________________________________________________________________");
                        // Execute Reader Method
                        spemp.StoredProcedureMonth();
                        break;

                    case 2:
                        Console.WriteLine();
                        Console.WriteLine(" Query for Employee with Product OwnerShip for a particular Year");
                        Console.WriteLine("_________________________________________________________________");
                        // Execute Reader Method
                        spemp.StoredProcedureYear();
                        break;

                    default:
                        Console.WriteLine("{0} is not a valid choice", myChoice);
                        break;


                }
                //Pause to allow the user to see the results
                Console.Write("press Enter key to continue...");
                Console.ReadLine();
                Console.WriteLine();
            } while (myChoice != 3); // Keep going until the user wants to quit
            Console.WriteLine("InValid Choice");

        }
        // Run Stored Procedure

        public void StoredProcedureYear()
        {
            MySqlConnection conn = null;
            MySqlDataReader rdr = null;

            try
            {
                // Create and open connection object

                conn = ConnectToDbClass.ConnectDb();

                conn.Open();

                // Create Command Object identifying the Stored Procedure 

                MySqlCommand cmd = new MySqlCommand("spempdate", conn);


                // Set the Command  Object to execute a Stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                //Define Parameters Used

                MySqlParameter param = new MySqlParameter();

                System.Console.WriteLine("List of Employees with product ownership for a Particular Year");

                Console.WriteLine("----------------------------------------------------------------------");


                System.Console.WriteLine("Enter the Year ");



                int yr = Convert.ToInt32(Console.ReadLine());

                param.ParameterName = "?uyear";
                param.Value = yr;

                cmd.Parameters.Add(param);


                // Execute The Command

                rdr = cmd.ExecuteReader();
                //Console.WriteLine();
                // Iterate thru results
                while (rdr.Read())
                {
                    //get the columns
                    int empid = (int)rdr["emp_id"];
                    DateTime owndate = (DateTime)(rdr[1]);
                    int prod_id = (int)rdr["prod_id"];

                    Console.WriteLine();
                    Console.WriteLine();
                    Console.WriteLine("Employee Id {0} ", empid);
                    Console.WriteLine("OwnnerShip Date {0} ", owndate);
                    Console.WriteLine("Product Id {0}", prod_id);
                    Console.ReadLine();

                }
            }

            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                if (rdr != null)
                {
                    rdr.Close();
                }
            }
        }

        // Run Stored Procedure
        public void StoredProcedureMonth()
        {
            MySqlConnection conn = null;
            MySqlDataReader rdr = null;

            try
            {
                // Create and open connection object

                conn = ConnectToDbClass.ConnectDb();

                conn.Open();

                // Create Command Object identifying the Stored Procedure 

                MySqlCommand cmd = new MySqlCommand("spempmonth", conn);


                // Set the Command  Object to execute a Stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                //Define Parameters Used

                MySqlParameter param = new MySqlParameter();

                System.Console.WriteLine("List of Employees with product ownership for a Particular Month");

                Console.WriteLine("----------------------------------------------------------------------");


                System.Console.WriteLine("Enter the Month");
                string monthname = Console.ReadLine();

                param.ParameterName = "?umonth";
                param.Value = monthname;

                cmd.Parameters.Add(param);


                // Execute The Command

                rdr = cmd.ExecuteReader();
                //Console.WriteLine();
                // Iterate thru results
                while (rdr.Read())
                {

                    //get the columns
                    int empid = (int)rdr["emp_id"];
                    DateTime owndate = (DateTime)(rdr[1]);
                    int prod_id = (int)rdr["prod_id"];

                    Console.WriteLine();
                    Console.WriteLine();
                    Console.WriteLine("Employee Id {0} ", empid);
                    Console.WriteLine("OwnnerShip Date {0} ", owndate);
                    Console.WriteLine("Product Id {0}", prod_id);
                    Console.ReadLine();

                }
            }

            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                if (rdr != null)
                {
                    rdr.Close();
                }
            }
        }
    }















}
